In [85]:
import numpy as np
import pandas as pd
import geopandas as gpd
from geopandas import GeoDataFrame, read_file
import shapely as shp
from shapely.geometry import Point, LineString, Polygon
from datetime import datetime, timedelta
import movingpandas as mpd
import hvplot.pandas 
# import folium
import contextily as cx
import matplotlib.pyplot as plt

import warnings
warnings.filterwarnings('ignore')

print(f'MovingPandas version {mpd.__version__}')
print(f'GeoPandas version {gpd.__version__}')
print(f'Shapely version {shp.__version__}')

MovingPandas version 0.8.rc1
GeoPandas version 0.9.0
Shapely version 1.7.1

Isikeliame Vilniaus tarnsporto stoteliu sluoksni¶

In [86]:
taskai = gpd.read_file('B:\\...Python\\Baigiamasis\\Duomenys\\gtfs_vln_marsrutai\\gis\\stops.gpkg')
ax = taskai.plot(figsize=(10, 10), alpha=0.5, edgecolor='k')
In [87]:
taskai.head(3)  # 1427 eilutes
Out[87]:
OBJECTID stop_id stop_code stop_name stop_desc stop_lat stop_lon stop_url location_type parent_station geometry
0 1 9483 None 1-asis Lentvaris None 54.64437 25.06552 https://www.stops.lt/vilnius/index.html#stop/3906 None None POINT (568773.727 6056951.539)
1 2 9560 None AP vartai None 54.72120 25.29556 https://www.stops.lt/vilnius/index.html#stop/0018 None None POINT (583463.063 6065751.175)
2 3 9102 None Bijūnų st. None 54.66740 25.21588 https://www.stops.lt/vilnius/index.html#stop/1631 None None POINT (578433.855 6059672.066)

Pravalome stoteliu dublikatus pagal stop_name, nes vienoje vietoje gali buti pvz. min 4 stoteles (pvz Stotis)¶

In [88]:
taskai.drop_duplicates('stop_name', inplace=True)
taskai.reset_index(inplace=True)
taskai.head(3)
# rezultatas - 706 stoteles
Out[88]:
index OBJECTID stop_id stop_code stop_name stop_desc stop_lat stop_lon stop_url location_type parent_station geometry
0 0 1 9483 None 1-asis Lentvaris None 54.64437 25.06552 https://www.stops.lt/vilnius/index.html#stop/3906 None None POINT (568773.727 6056951.539)
1 1 2 9560 None AP vartai None 54.72120 25.29556 https://www.stops.lt/vilnius/index.html#stop/0018 None None POINT (583463.063 6065751.175)
2 2 3 9102 None Bijūnų st. None 54.66740 25.21588 https://www.stops.lt/vilnius/index.html#stop/1631 None None POINT (578433.855 6059672.066)

BUFERIO kurimas¶

In [89]:
# Sukuriame atskira buferini sluoksni/lentele, kuriame priskirsime buferi
buf = taskai
buf.head(3)
Out[89]:
index OBJECTID stop_id stop_code stop_name stop_desc stop_lat stop_lon stop_url location_type parent_station geometry
0 0 1 9483 None 1-asis Lentvaris None 54.64437 25.06552 https://www.stops.lt/vilnius/index.html#stop/3906 None None POINT (568773.727 6056951.539)
1 1 2 9560 None AP vartai None 54.72120 25.29556 https://www.stops.lt/vilnius/index.html#stop/0018 None None POINT (583463.063 6065751.175)
2 2 3 9102 None Bijūnų st. None 54.66740 25.21588 https://www.stops.lt/vilnius/index.html#stop/1631 None None POINT (578433.855 6059672.066)
In [90]:
# buf sluoksniui/lentai paskaiciuojame 500 metru buferi 

buf['buffer_geometry'] = buf.geometry.buffer(500)
buf.head(3)
Out[90]:
index OBJECTID stop_id stop_code stop_name stop_desc stop_lat stop_lon stop_url location_type parent_station geometry buffer_geometry
0 0 1 9483 None 1-asis Lentvaris None 54.64437 25.06552 https://www.stops.lt/vilnius/index.html#stop/3906 None None POINT (568773.727 6056951.539) POLYGON ((569273.727 6056951.539, 569271.320 6...
1 1 2 9560 None AP vartai None 54.72120 25.29556 https://www.stops.lt/vilnius/index.html#stop/0018 None None POINT (583463.063 6065751.175) POLYGON ((583963.063 6065751.175, 583960.655 6...
2 2 3 9102 None Bijūnų st. None 54.66740 25.21588 https://www.stops.lt/vilnius/index.html#stop/1631 None None POINT (578433.855 6059672.066) POLYGON ((578933.855 6059672.066, 578931.448 6...
In [91]:
# istriname taskinio sluoksnio GEOMETRY stulpeli, kad nesumaisytu pandoms plotinant
buf = buf.drop(columns=['geometry'])
buf.reset_index(inplace=True)
buf
Out[91]:
level_0 index OBJECTID stop_id stop_code stop_name stop_desc stop_lat stop_lon stop_url location_type parent_station buffer_geometry
0 0 0 1 9483 None 1-asis Lentvaris None 54.64437 25.06552 https://www.stops.lt/vilnius/index.html#stop/3906 None None POLYGON ((569273.727 6056951.539, 569271.320 6...
1 1 1 2 9560 None AP vartai None 54.72120 25.29556 https://www.stops.lt/vilnius/index.html#stop/0018 None None POLYGON ((583963.063 6065751.175, 583960.655 6...
2 2 2 3 9102 None Bijūnų st. None 54.66740 25.21588 https://www.stops.lt/vilnius/index.html#stop/1631 None None POLYGON ((578933.855 6059672.066, 578931.448 6...
3 3 3 4 16291 None Geležinio Vilko st. None 54.75762 25.27183 https://www.stops.lt/vilnius/index.html#stop/2145 None None POLYGON ((582360.929 6069776.295, 582358.521 6...
4 4 5 6 8258 None Kaimelio žiedas None 54.73141 25.24430 https://www.stops.lt/vilnius/index.html#stop/3890 None None POLYGON ((580640.799 6066827.662, 580638.391 6...
... ... ... ... ... ... ... ... ... ... ... ... ... ...
701 701 1413 1414 814 None Panorama None 54.69915 25.25704 https://www.stops.lt/vilnius/index.html#stop/0317 None None POLYGON ((581525.598 6063252.164, 581523.190 6...
702 702 1415 1416 6397 None Švietimo akademija None 54.69830 25.26121 https://www.stops.lt/vilnius/index.html#stop/0318 None None POLYGON ((581796.071 6063162.392, 581793.663 6...
703 703 1421 1422 632 None Džocharo Dudajevo skv. None 54.69583 25.25609 https://www.stops.lt/vilnius/index.html#stop/0309 None None POLYGON ((581470.978 6062881.596, 581468.570 6...
704 704 1423 1424 1116 None Vytauto st. None 54.69117 25.25643 https://www.stops.lt/vilnius/index.html#stop/0305 None None POLYGON ((581502.176 6062363.392, 581499.768 6...
705 705 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None None POLYGON ((582502.179 6063868.678, 582499.772 6...

706 rows × 13 columns

In [92]:
# plotinam buferius
ax = buf['buffer_geometry'].plot(figsize=(20, 20))
In [93]:
# plotinam buferius su stoteliu taskais
ax = buf['buffer_geometry'].plot(figsize=(15,15), color = 'lightblue')
taskai['geometry'].plot(ax = ax, color = 'red')
plt.title('300 m buferis')
Out[93]:
Text(0.5, 1.0, '300 m buferis')
In [94]:
# Isikeliu 2021 metu preliminarius gyventoju surasymo duomenis gride ir Vilniaus ribas
grid2011 = gpd.read_file('B:\\...\\Python\\Baigiamasis\\Duomenys\\gtfs_vln_marsrutai\\grid2021\\grid2011.gpkg')
vilnius = gpd.read_file('B:\\...\\Python\\Baigiamasis\\Duomenys\\gtfs_vln_marsrutai\\grid2021\\Vilnius.gpkg')
gx = grid2011.plot(figsize=(7, 7), alpha=0.5, edgecolor='k')
vilnius['geometry'].plot(ax = gx, color = 'lightgreen', alpha = 0.5)
buf['buffer_geometry'].plot(ax = gx, color = 'yellow')
plt.title('Gyventoju surasymo tinklelis, Vilniaus miesto ribos, stoteliu buferiai')
Out[94]:
Text(0.5, 1.0, 'Gyventoju surasymo tinklelis, Vilniaus miesto ribos, stoteliu buferiai')
In [95]:
grid2011.head(5)
Out[95]:
OBJECTID GRID_ID GYV_SK VYRU_SK MOTERU_SK AMZIUS0_14 AMZIUS15_6 AMZIUS65 AUKSTASIS AUKSTESNYS ... GYV_TANKIS BUSTU_SK BUTAS1 BUTAS2 BUTAS_ VANDUO VONIA wc GYV_BUSTAS geometry
0 12794 100x100mX56690Y60549 < 10 < 10 0 0 < 10 0 0 0 ... Duomenys neteikiami < 4 < 4 0 0 0 0 0 <= 3 MULTIPOLYGON (((566900.000 6054991.045, 566900...
1 12795 100x100mX56730Y60551 < 10 < 10 < 10 < 10 < 10 0 0 0 ... Duomenys neteikiami < 4 < 4 0 0 0 0 0 <= 3 MULTIPOLYGON (((567300.000 6055191.045, 567300...
2 12796 100x100mX56800Y60548 0 0 0 0 0 0 0 0 ... 0 < 4 0 < 4 0 0 0 0 0 MULTIPOLYGON (((568000.000 6054891.045, 568000...
3 12797 100x100mX56860Y60543 16 < 10 < 10 < 10 10 - 19 < 10 < 10 0 ... 1600 < 4 < 4 0 0 0 0 0 <= 5 MULTIPOLYGON (((568600.000 6054391.045, 568600...
4 12798 100x100mX56860Y60549 0 0 0 0 0 0 0 0 ... 0 < 4 < 4 0 0 0 0 0 0 MULTIPOLYGON (((568600.000 6054991.045, 568600...

5 rows × 38 columns

Sukertame (INTERSECT) buferio sluoksni su gridu¶

In [96]:
buf = buf.set_geometry('buffer_geometry')
In [97]:
# vykdant Intersect, mete klaida apie jau egzistuojanti Level_0 indexa. Ji nudropinau
buf.drop(['level_0'], axis = 1, inplace = True)
buf
Out[97]:
index OBJECTID stop_id stop_code stop_name stop_desc stop_lat stop_lon stop_url location_type parent_station buffer_geometry
0 0 1 9483 None 1-asis Lentvaris None 54.64437 25.06552 https://www.stops.lt/vilnius/index.html#stop/3906 None None POLYGON ((569273.727 6056951.539, 569271.320 6...
1 1 2 9560 None AP vartai None 54.72120 25.29556 https://www.stops.lt/vilnius/index.html#stop/0018 None None POLYGON ((583963.063 6065751.175, 583960.655 6...
2 2 3 9102 None Bijūnų st. None 54.66740 25.21588 https://www.stops.lt/vilnius/index.html#stop/1631 None None POLYGON ((578933.855 6059672.066, 578931.448 6...
3 3 4 16291 None Geležinio Vilko st. None 54.75762 25.27183 https://www.stops.lt/vilnius/index.html#stop/2145 None None POLYGON ((582360.929 6069776.295, 582358.521 6...
4 5 6 8258 None Kaimelio žiedas None 54.73141 25.24430 https://www.stops.lt/vilnius/index.html#stop/3890 None None POLYGON ((580640.799 6066827.662, 580638.391 6...
... ... ... ... ... ... ... ... ... ... ... ... ...
701 1413 1414 814 None Panorama None 54.69915 25.25704 https://www.stops.lt/vilnius/index.html#stop/0317 None None POLYGON ((581525.598 6063252.164, 581523.190 6...
702 1415 1416 6397 None Švietimo akademija None 54.69830 25.26121 https://www.stops.lt/vilnius/index.html#stop/0318 None None POLYGON ((581796.071 6063162.392, 581793.663 6...
703 1421 1422 632 None Džocharo Dudajevo skv. None 54.69583 25.25609 https://www.stops.lt/vilnius/index.html#stop/0309 None None POLYGON ((581470.978 6062881.596, 581468.570 6...
704 1423 1424 1116 None Vytauto st. None 54.69117 25.25643 https://www.stops.lt/vilnius/index.html#stop/0305 None None POLYGON ((581502.176 6062363.392, 581499.768 6...
705 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None None POLYGON ((582502.179 6063868.678, 582499.772 6...

706 rows × 12 columns

In [98]:
susikirtimas = gpd.overlay(buf, grid2011, how='intersection')
susikirtimas.head(3)
Out[98]:
index OBJECTID_1 stop_id stop_code stop_name stop_desc stop_lat stop_lon stop_url location_type ... GYV_TANKIS BUSTU_SK BUTAS1 BUTAS2 BUTAS_ VANDUO VONIA wc GYV_BUSTAS geometry
0 1 2 9560 None AP vartai None 54.72120 25.29556 https://www.stops.lt/vilnius/index.html#stop/0018 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((582989.955 6065591.045, 582984.593 6...
1 141 142 5839 None Naujakiemio st. None 54.72387 25.28144 https://www.stops.lt/vilnius/index.html#stop/2012 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((582913.370 6065691.045, 582901.597 6...
2 1329 1330 5846 None Jono Kazlausko st. None 54.72145 25.28502 https://www.stops.lt/vilnius/index.html#stop/2014 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((582900.000 6065591.045, 582900.000 6...

3 rows × 49 columns

In [99]:
list(susikirtimas.columns)
Out[99]:
['index',
 'OBJECTID_1',
 'stop_id',
 'stop_code',
 'stop_name',
 'stop_desc',
 'stop_lat',
 'stop_lon',
 'stop_url',
 'location_type',
 'parent_station',
 'OBJECTID_2',
 'GRID_ID',
 'GYV_SK',
 'VYRU_SK',
 'MOTERU_SK',
 'AMZIUS0_14',
 'AMZIUS15_6',
 'AMZIUS65',
 'AUKSTASIS',
 'AUKSTESNYS',
 'VIDURINIS',
 'PAGRINDINI',
 'DARBAS',
 'VERSLAS',
 'PENSIJA',
 'ISLAIKOMAS',
 'SUSITUOKE',
 'ISSITUOKE',
 'NASLIAI',
 'NESUSITUOK',
 'LT_K',
 'POL_K',
 'RU_K',
 'KITA_K',
 'EN_MK',
 'POL_MK',
 'RU_MK',
 'KITA_MK',
 'GYV_TANKIS',
 'BUSTU_SK',
 'BUTAS1',
 'BUTAS2',
 'BUTAS_',
 'VANDUO',
 'VONIA',
 'wc',
 'GYV_BUSTAS',
 'geometry']
In [100]:
#ax = vilnius['geometry'].plot( color = 'red', alpha = 0.5)
susikirtimas.plot(figsize=(15, 15), alpha=0.5, edgecolor='k')

#buf['buffer_geometry'].plot(ax = gx, color = 'yellow')
Out[100]:
<AxesSubplot:>
In [40]:
# sluoksnio eksportas i GIS formata
import fiona

susikirtimas.to_file('susikirtim34.geojson', driver='GeoJSON')
In [101]:
# "susikirtimas" gyventoju surasymo duomenyse pasitaike reiksmiu "<5", 
# kas veliau trukdys atlikti statistikos skaiciavimus. Reikalimgas duomenu pravalymas

# 1. Susikuriu susikirtimo sluoksnio kopija, kurioje vykdysiu valymus
susikirtimas2 = susikirtimas
susikirtimas2.head(3)
Out[101]:
index OBJECTID_1 stop_id stop_code stop_name stop_desc stop_lat stop_lon stop_url location_type ... GYV_TANKIS BUSTU_SK BUTAS1 BUTAS2 BUTAS_ VANDUO VONIA wc GYV_BUSTAS geometry
0 1 2 9560 None AP vartai None 54.72120 25.29556 https://www.stops.lt/vilnius/index.html#stop/0018 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((582989.955 6065591.045, 582984.593 6...
1 141 142 5839 None Naujakiemio st. None 54.72387 25.28144 https://www.stops.lt/vilnius/index.html#stop/2012 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((582913.370 6065691.045, 582901.597 6...
2 1329 1330 5846 None Jono Kazlausko st. None 54.72145 25.28502 https://www.stops.lt/vilnius/index.html#stop/2014 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((582900.000 6065591.045, 582900.000 6...

3 rows × 49 columns

In [102]:
list(susikirtimas2.columns)
Out[102]:
['index',
 'OBJECTID_1',
 'stop_id',
 'stop_code',
 'stop_name',
 'stop_desc',
 'stop_lat',
 'stop_lon',
 'stop_url',
 'location_type',
 'parent_station',
 'OBJECTID_2',
 'GRID_ID',
 'GYV_SK',
 'VYRU_SK',
 'MOTERU_SK',
 'AMZIUS0_14',
 'AMZIUS15_6',
 'AMZIUS65',
 'AUKSTASIS',
 'AUKSTESNYS',
 'VIDURINIS',
 'PAGRINDINI',
 'DARBAS',
 'VERSLAS',
 'PENSIJA',
 'ISLAIKOMAS',
 'SUSITUOKE',
 'ISSITUOKE',
 'NASLIAI',
 'NESUSITUOK',
 'LT_K',
 'POL_K',
 'RU_K',
 'KITA_K',
 'EN_MK',
 'POL_MK',
 'RU_MK',
 'KITA_MK',
 'GYV_TANKIS',
 'BUSTU_SK',
 'BUTAS1',
 'BUTAS2',
 'BUTAS_',
 'VANDUO',
 'VONIA',
 'wc',
 'GYV_BUSTAS',
 'geometry']

Nepageidaujamu grido intervalu naikinimas¶

funkcijos sudarymas ir testavimas¶

In [103]:
# paziuriu kiekvieno stulpelio unikalius. 
susikirtimas2['MOTERU_SK'].value_counts().sort_values(ascending = False)

# rezultate matosi, kad yra neskaitines reiksmes, pvz <10, 10-19 ir pan.
Out[103]:
< 10         10177
10 - 19       3202
0             1469
20 - 29       1440
30 - 39        857
50 - 59        841
40 - 49        788
60 - 69        720
80 - 89        619
70 - 79        618
90 - 99        529
110 - 119      484
100 - 109      445
130 - 139      435
120 - 129      390
140 - 149      288
150 - 159      264
180 - 189      261
160 - 169      214
190 - 199      213
170 - 179      184
210 - 219      174
200 - 209      164
220 - 229      129
230 - 239      118
250 - 259      106
240 - 249      101
270 - 279       94
260 - 269       79
300 - 309       59
280 - 289       56
290 - 299       55
330 - 339       36
350 - 359       36
320 - 329       31
340 - 349       23
360 - 369       18
310 - 319       17
380 - 389       13
420 - 429       11
430 - 439        8
410 - 419        8
400 - 409        8
370 - 379        8
590 - 599        7
390 - 399        6
510 - 519        5
450 - 459        4
Name: MOTERU_SK, dtype: int64
In [104]:
# pakeisime duomenis:  <10 -> 10, is 10-19 -> 14 (vidutine reiksme truncate-inta)
keitimui = susikirtimas2
keitimui
Out[104]:
index OBJECTID_1 stop_id stop_code stop_name stop_desc stop_lat stop_lon stop_url location_type ... GYV_TANKIS BUSTU_SK BUTAS1 BUTAS2 BUTAS_ VANDUO VONIA wc GYV_BUSTAS geometry
0 1 2 9560 None AP vartai None 54.72120 25.29556 https://www.stops.lt/vilnius/index.html#stop/0018 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((582989.955 6065591.045, 582984.593 6...
1 141 142 5839 None Naujakiemio st. None 54.72387 25.28144 https://www.stops.lt/vilnius/index.html#stop/2012 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((582913.370 6065691.045, 582901.597 6...
2 1329 1330 5846 None Jono Kazlausko st. None 54.72145 25.28502 https://www.stops.lt/vilnius/index.html#stop/2014 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((582900.000 6065591.045, 582900.000 6...
3 1337 1338 5127 None Verkių st. None 54.71687 25.29360 https://www.stops.lt/vilnius/index.html#stop/0807 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((582965.410 6065591.045, 582992.131 6...
4 1343 1344 5022 None Autobusų parkas None 54.72092 25.29589 https://www.stops.lt/vilnius/index.html#stop/5177 None ... Duomenys neteikiami < 4 0 0 < 4 0 0 0 <= 3 POLYGON ((583000.000 6065600.920, 582994.504 6...
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
25807 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None ... 1300 6 0 < 4 4 4 < 4 4 2 POLYGON ((582000.000 6064091.045, 582000.000 6...
25808 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None ... Duomenys neteikiami < 4 < 4 0 0 0 < 4 < 4 <= 3 POLYGON ((582100.000 6063891.045, 582100.000 6...
25809 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None ... 3100 9 < 4 6 0 6 6 6 3 POLYGON ((582100.000 6064191.045, 582100.000 6...
25810 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None ... 3500 12 < 4 4 5 4 11 6 3 POLYGON ((582200.000 6064091.045, 582200.000 6...
25811 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None ... 5600 15 10 0 5 13 13 13 4 POLYGON ((582268.898 6064291.045, 582279.964 6...

25812 rows × 49 columns

In [118]:
def keitimasm(row):
    if '-' in row['MOTERU_SK']:
        sp = row['MOTERU_SK'].split()
        vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
        return vidurkis
    elif '<' in row['MOTERU_SK']:
        rep = row['MOTERU_SK'].replace('<','')
        return int(rep)
    else:
        return int(row['MOTERU_SK'])
In [119]:
#pritaikau funkcija moteru stulpeliui 

keitimui['MOTERU_SK2'] = keitimui.apply(keitimasm, axis = 'columns')
In [120]:
# paziuriu ar pavyko pakeisti:

keitimui.loc[:, ['MOTERU_SK', 'MOTERU_SK2']]
Out[120]:
MOTERU_SK MOTERU_SK2
0 0 0.0
1 0 0.0
2 0 0.0
3 0 0.0
4 0 0.0
... ... ...
25807 < 10 10.0
25808 < 10 10.0
25809 10 - 19 14.0
25810 20 - 29 24.0
25811 20 - 29 24.0

25812 rows × 2 columns

pritaikau funkcija visiems pageidaujamiems susikirtimas2 stulpeliams¶

In [121]:
# moterims

def keitimasm(row):
    if '-' in row['MOTERU_SK']:
        sp = row['MOTERU_SK'].split()
        vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
        return vidurkis
    elif '<' in row['MOTERU_SK']:
        rep = row['MOTERU_SK'].replace('<','')
        return int(rep)
    else:
        return int(row['MOTERU_SK'])
    
    
susikirtimas2['MOTERU_SK2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
In [122]:
# gyventoju skaiciaus stulpeliui

def keitimasm(row):
    if '-' in row['GYV_SK']:
        sp = row['GYV_SK'].split()
        vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
        return vidurkis
    elif '<' in row['GYV_SK']:
        rep = row['GYV_SK'].replace('<','')
        return int(rep)
    else:
        return int(row['GYV_SK'])
    
    
susikirtimas2['GYV_SK2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
In [123]:
# vyrams

def keitimasm(row):
    if '-' in row['VYRU_SK']:
        sp = row['VYRU_SK'].split()
        vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
        return vidurkis
    elif '<' in row['VYRU_SK']:
        rep = row['VYRU_SK'].replace('<','')
        return int(rep)
    else:
        return int(row['VYRU_SK'])
    
    
susikirtimas2['VYRU_SK2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
In [124]:
# AMZIUS0_14

def keitimasm(row):
    if '-' in row['AMZIUS0_14']:
        sp = row['AMZIUS0_14'].split()
        vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
        return vidurkis
    elif '<' in row['AMZIUS0_14']:
        rep = row['AMZIUS0_14'].replace('<','')
        return int(rep)
    else:
        return int(row['AMZIUS0_14'])
    
    
susikirtimas2['AMZIUS0_14_2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
In [125]:
# AMZIUS15_6

def keitimasm(row):
    if '-' in row['AMZIUS15_6']:
        sp = row['AMZIUS15_6'].split()
        vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
        return vidurkis
    elif '<' in row['AMZIUS15_6']:
        rep = row['AMZIUS15_6'].replace('<','')
        return int(rep)
    else:
        return int(row['AMZIUS15_6'])
    
    
susikirtimas2['AMZIUS15_6_2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
In [126]:
# AMZIUS65

def keitimasm(row):
    if '-' in row['AMZIUS65']:
        sp = row['AMZIUS65'].split()
        vidurkis = np.trunc((int(sp[0])+int(sp[2]))/2)
        return vidurkis
    elif '<' in row['AMZIUS65']:
        rep = row['AMZIUS65'].replace('<','')
        return int(rep)
    else:
        return int(row['AMZIUS65'])
    
    
susikirtimas2['AMZIUS65_2'] = susikirtimas2.apply(keitimasm, axis = 'columns')
In [127]:
susikirtimas2
Out[127]:
index OBJECTID_1 stop_id stop_code stop_name stop_desc stop_lat stop_lon stop_url location_type ... VONIA wc GYV_BUSTAS geometry MOTERU_SK2 GYV_SK2 VYRU_SK2 AMZIUS0_14_2 AMZIUS15_6_2 AMZIUS65_2
0 1 2 9560 None AP vartai None 54.72120 25.29556 https://www.stops.lt/vilnius/index.html#stop/0018 None ... 0 0 <= 3 POLYGON ((582989.955 6065591.045, 582984.593 6... 0.0 10 10.0 0.0 10.0 0.0
1 141 142 5839 None Naujakiemio st. None 54.72387 25.28144 https://www.stops.lt/vilnius/index.html#stop/2012 None ... 0 0 <= 3 POLYGON ((582913.370 6065691.045, 582901.597 6... 0.0 10 10.0 0.0 10.0 0.0
2 1329 1330 5846 None Jono Kazlausko st. None 54.72145 25.28502 https://www.stops.lt/vilnius/index.html#stop/2014 None ... 0 0 <= 3 POLYGON ((582900.000 6065591.045, 582900.000 6... 0.0 10 10.0 0.0 10.0 0.0
3 1337 1338 5127 None Verkių st. None 54.71687 25.29360 https://www.stops.lt/vilnius/index.html#stop/0807 None ... 0 0 <= 3 POLYGON ((582965.410 6065591.045, 582992.131 6... 0.0 10 10.0 0.0 10.0 0.0
4 1343 1344 5022 None Autobusų parkas None 54.72092 25.29589 https://www.stops.lt/vilnius/index.html#stop/5177 None ... 0 0 <= 3 POLYGON ((583000.000 6065600.920, 582994.504 6... 0.0 10 10.0 0.0 10.0 0.0
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
25807 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None ... < 4 4 2 POLYGON ((582000.000 6064091.045, 582000.000 6... 10.0 13 10.0 0.0 10.0 10.0
25808 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None ... < 4 < 4 <= 3 POLYGON ((582100.000 6063891.045, 582100.000 6... 10.0 10 10.0 0.0 10.0 10.0
25809 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None ... 6 6 3 POLYGON ((582100.000 6064191.045, 582100.000 6... 14.0 31 14.0 10.0 24.0 10.0
25810 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None ... 11 6 3 POLYGON ((582200.000 6064091.045, 582200.000 6... 24.0 35 14.0 10.0 24.0 10.0
25811 1425 1426 5462 None Linkmenų st. None 54.70453 25.27236 https://www.stops.lt/vilnius/index.html#stop/0702 None ... 13 13 4 POLYGON ((582268.898 6064291.045, 582279.964 6... 24.0 56 24.0 14.0 34.0 10.0

25812 rows × 55 columns

In [128]:
list(susikirtimas2.columns)
Out[128]:
['index',
 'OBJECTID_1',
 'stop_id',
 'stop_code',
 'stop_name',
 'stop_desc',
 'stop_lat',
 'stop_lon',
 'stop_url',
 'location_type',
 'parent_station',
 'OBJECTID_2',
 'GRID_ID',
 'GYV_SK',
 'VYRU_SK',
 'MOTERU_SK',
 'AMZIUS0_14',
 'AMZIUS15_6',
 'AMZIUS65',
 'AUKSTASIS',
 'AUKSTESNYS',
 'VIDURINIS',
 'PAGRINDINI',
 'DARBAS',
 'VERSLAS',
 'PENSIJA',
 'ISLAIKOMAS',
 'SUSITUOKE',
 'ISSITUOKE',
 'NASLIAI',
 'NESUSITUOK',
 'LT_K',
 'POL_K',
 'RU_K',
 'KITA_K',
 'EN_MK',
 'POL_MK',
 'RU_MK',
 'KITA_MK',
 'GYV_TANKIS',
 'BUSTU_SK',
 'BUTAS1',
 'BUTAS2',
 'BUTAS_',
 'VANDUO',
 'VONIA',
 'wc',
 'GYV_BUSTAS',
 'geometry',
 'MOTERU_SK2',
 'GYV_SK2',
 'VYRU_SK2',
 'AMZIUS0_14_2',
 'AMZIUS15_6_2',
 'AMZIUS65_2']
In [129]:
eksportui_intersectas = susikirtimas2.loc[:, ['index',
 'stop_id',
 'stop_code',
 'stop_name',
 'stop_desc',
 'stop_lat',
 'stop_lon',
 'stop_url',
  'GRID_ID',
 'GYV_SK',
 'VYRU_SK',
 'MOTERU_SK',
 'AMZIUS0_14',
 'AMZIUS15_6',
 'AMZIUS65',
 'AUKSTASIS',
 'AUKSTESNYS',
 'VIDURINIS',
 'PAGRINDINI',
 'DARBAS',
 'VERSLAS',
 'PENSIJA',
 'geometry',
 'MOTERU_SK2',
 'GYV_SK2',
 'VYRU_SK2',
 'AMZIUS0_14_2',
 'AMZIUS15_6_2',
 'AMZIUS65_2']]
In [130]:
# issieksportuoju kaip csv isidejimui i Power BI.

eksportui_intersectas.to_csv('susikirtimas3.csv')
In [ ]:

Decision tree classifier¶

In [ ]:
# https://youtu.be/PHxYNGo8NcI

In [152]:
# Prisijungiu prie duomenu is MYSQL DB ir atsidarau lentele "gridas_klasifikavimui"
import mysql.connector
import pandas as pd

mydb = mysql.connector.connect(
    host = 'localhost',
    port = '3317',
    user = 'root',
    password = '***********'
)

cursor = mydb.cursor()
cursor.execute('USE projektas') 

df = pd.read_sql('SELECT GRID_ID, mokyklos, universitetas, darzeliai, gydymo_istaiga, prioritetas from gridas_klasifikavimui', con = mydb)
df
Out[152]:
GRID_ID mokyklos universitetas darzeliai gydymo_istaiga prioritetas
0 100x100mX57780Y60605 1 1 1 1 4
1 100x100mX57790Y60601 1 1 1 1 4
2 100x100mX57790Y60605 1 1 1 1 4
3 100x100mX57790Y60606 1 1 1 1 4
4 100x100mX57800Y60601 1 1 1 1 4
... ... ... ... ... ... ...
12625 100x100mX59540Y60623 0 0 0 0 0
12626 100x100mX59540Y60624 0 0 0 0 0
12627 100x100mX59540Y60625 0 0 0 0 0
12628 100x100mX59540Y60627 0 0 0 0 0
12629 100x100mX59540Y60628 0 0 0 0 0

12630 rows × 6 columns

In [153]:
# independable variable

inputs = df.drop(['prioritetas','GRID_ID'], axis = 'columns')

target = df['prioritetas']
In [154]:
inputs.head(3)
Out[154]:
mokyklos universitetas darzeliai gydymo_istaiga
0 1 1 1 1
1 1 1 1 1
2 1 1 1 1
In [155]:
target.head(3)
Out[155]:
0    4
1    4
2    4
Name: prioritetas, dtype: int64
In [156]:
#train 
from sklearn import tree
In [157]:
model = tree.DecisionTreeClassifier()
In [158]:
model.fit(inputs, target)
Out[158]:
DecisionTreeClassifier()
In [159]:
# score
model.score(inputs, target)
Out[159]:
1.0
In [160]:
# predict
model.predict([[0,0,0,0]])
Out[160]:
array([0], dtype=int64)
In [161]:
model.predict([[0,0,1,0]])
Out[161]:
array([1], dtype=int64)
In [162]:
model.predict([[1,1,0,0]])
Out[162]:
array([2], dtype=int64)
In [163]:
model.predict([[1,1,1,0]])
Out[163]:
array([3], dtype=int64)
In [164]:
model.predict([[1,1,1,1]])
Out[164]:
array([4], dtype=int64)
In [165]:
prediction = model.predict(inputs)
df['Prediction'] = prediction
df
Out[165]:
GRID_ID mokyklos universitetas darzeliai gydymo_istaiga prioritetas Prediction
0 100x100mX57780Y60605 1 1 1 1 4 4
1 100x100mX57790Y60601 1 1 1 1 4 4
2 100x100mX57790Y60605 1 1 1 1 4 4
3 100x100mX57790Y60606 1 1 1 1 4 4
4 100x100mX57800Y60601 1 1 1 1 4 4
... ... ... ... ... ... ... ...
12625 100x100mX59540Y60623 0 0 0 0 0 0
12626 100x100mX59540Y60624 0 0 0 0 0 0
12627 100x100mX59540Y60625 0 0 0 0 0 0
12628 100x100mX59540Y60627 0 0 0 0 0 0
12629 100x100mX59540Y60628 0 0 0 0 0 0

12630 rows × 7 columns

In [166]:
df.to_csv('prediction.csv')
In [ ]: